/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import Model.Artist;
import Model.BestSeller;
import Model.Category;
import Model.Citys;
import Model.Customer;
import Model.Paymenttype;
//import Model.PaymenttypeReport;
import Model.Products;
import Model.SalesWeeks;
//import Model.SalesWeeks;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.Persistence;
import javax.persistence.Query;

/**
 *
 * @author MTI
 */
@ManagedBean(name = "reportController")
@SessionScoped
public class ReportController {

    private List<BestSeller> citySellers;
    private List<BestSeller> bestSellers;
    //private List<PaymenttypeReport> paymenttypeReport;
    private List<BestSeller> bestSellerArtists;
    private List<SalesWeeks> salesWeeks;
    EntityManager entityManager = Persistence.createEntityManagerFactory("KitapcdappPU").createEntityManager();

    public List<BestSeller> getBestSellers() {
        Query allProducts = entityManager.createNamedQuery("Products.findAll");
        List<Products> products = (List<Products>) allProducts.getResultList();
        bestSellers = new ArrayList<BestSeller>();
        for (Products product : products) {
            BestSeller bestseller = new BestSeller();
            Query query = entityManager.createNativeQuery("SELECT count(o.productid) FROM Orderproducts o where o.productid =" + product.getId() + " GROUP BY o.productid");

            bestseller.setUrunId(product.getId());
            int count = 0;
            try {
                count = (Integer) query.getSingleResult();
            } catch (NoResultException ex) {
                count = 0;
            }

            bestseller.setOrderCount(count);
            bestseller.setProductName(product.getName());
            bestSellers.add(bestseller);
        }
        return bestSellers;
    }
    
    public List<Products> getBestSellersProducts() {
        List<BestSeller> lbs = getBestSellers();
        List<Products> products = new ArrayList<Products>() {};
        Iterator<BestSeller> ilbs = lbs.iterator();
        while(ilbs.hasNext()) {
            Integer id = ilbs.next().getUrunId();
            Query query = entityManager.createNamedQuery("Products.findById");
            query.setParameter("id", id);
            products.addAll((List<Products>) query.getResultList());
        }
        return products;
    }

    public List<BestSeller> getBestSellerArtists() {
        Query allProducts = entityManager.createNamedQuery("Artist.findAll");
        List<Artist> products = (List<Artist>) allProducts.getResultList();
        bestSellerArtists = new ArrayList<BestSeller>();
        for (Artist product : products) {
            BestSeller bestseller = new BestSeller();
            Query query = entityManager.createNativeQuery("SELECT count(o.productid) FROM Orderproducts o "
                    + "inner join Artistproductmappings  on o.productid = Artistproductmappings.artistid"
                    + " where Artistproductmappings.artistid =" + product.getId());

            bestseller.setUrunId(product.getId());
            int count = 0;
            try {
                count = (Integer) query.getSingleResult();
            } catch (NoResultException ex) {
                count = 0;
            }

            bestseller.setOrderCount(count);
            bestseller.setProductName(product.getName() + product.getSurname());
            bestSellerArtists.add(bestseller);
        }
        return bestSellerArtists;
    }
    
    public List<Artist> getBestSellersArtistForReport() {
        List<BestSeller> lbs = getBestSellerArtists();
        List<Artist> artists = new ArrayList<Artist>() {};
        Iterator<BestSeller> ilbs = lbs.iterator();
        while(ilbs.hasNext()) {
            Integer id = ilbs.next().getUrunId();
            Query query = entityManager.createNamedQuery("Artist.findById");
            query.setParameter("id", id);
            artists.addAll((List<Artist>) query.getResultList());
        }
        return artists;
    }
    
    public List<BestSeller> getBestCustomerForFeedback() {
         Query allCustomerQuery = entityManager.createNamedQuery("Customer.findAll");
         List<Customer> customers = (List<Customer>) allCustomerQuery.getResultList();
         bestSellers = new ArrayList<BestSeller>();
         
         for (Customer customer : customers) {
            BestSeller bestseller = new BestSeller();
            Query query = entityManager.createNativeQuery("SELECT COUNT(customerid) FROM feedback f where f.customerid=" + customer.getId() + " GROUP BY f.customerid");
            bestseller.setUrunId(customer.getId());
            int count = 0;
            try {
                count = (Integer) query.getSingleResult();
            } catch (NoResultException ex) {
                count = 0;
            }

            bestseller.setOrderCount(count);
            bestseller.setProductName(customer.getName());
            bestSellers.add(bestseller);
        }
        return bestSellers;
    }
    
    public List<BestSeller> getBestOrderForCustomer() {
         Query allCustomerQuery = entityManager.createNamedQuery("Customer.findAll");
         List<Customer> customers = (List<Customer>) allCustomerQuery.getResultList();
         bestSellers = new ArrayList<BestSeller>();
         
         for (Customer customer : customers) {
            BestSeller bestseller = new BestSeller();
            Query query = entityManager.createNativeQuery("SELECT COUNT(price) FROM orderproducts o where o.price=" + customer.getId() + " GROUP BY o.price");
            bestseller.setUrunId(customer.getId());
            int count = 0;
            try {
                count = (Integer) query.getSingleResult();
            } catch (NoResultException ex) {
                count = 0;
            }

            bestseller.setOrderCount(count);
            bestseller.setProductName(customer.getName());
            bestSellers.add(bestseller);
        }
        return bestSellers;
    }

    /*public List<PaymenttypeReport> getSellersByPayment() {
        Query allPayments = entityManager.createNamedQuery("Paymenttype.findAll");
        List<Paymenttype> paymenttypes = (List<Paymenttype>) allPayments.getResultList();
        paymenttypeReport = new ArrayList<PaymenttypeReport>();
        for (Paymenttype paymenttype : paymenttypes) {
            PaymenttypeReport pt = new PaymenttypeReport();
            pt.setPaymentTypeId(paymenttype.getId());
            pt.setPaymentTypeName(paymenttype.getName());
            int count = paymenttype.getOrdersCollection().size();
            pt.setOrderCount(count);
            paymenttypeReport.add(pt);
        }
        return paymenttypeReport;
    }*/

    public List<SalesWeeks> getSalesByWeek() throws ParseException {
        Calendar c = Calendar.getInstance();
        Date dt = new Date();
        c.setTime(dt);
        salesWeeks = new ArrayList<SalesWeeks>();
        for (int i = 0; i < 8; i++) {
            SalesWeeks sw = new SalesWeeks();
            sw.setStartdate(dt);
            Timestamp tstart = dateToTime(sw.getStartdate());
            Timestamp tend = dateToTime(sw.getEnddate());
            Query salesbyweeks = entityManager.createNativeQuery("select count(*) from Orders o where o.createddate < '" + tend + "' and '"
                    + tstart + "' < o.createddate");
            int z = (Integer) salesbyweeks.getSingleResult();
            c.add(Calendar.DATE, -7);
            dt = c.getTime();
            sw.setOrderCount(z);
            salesWeeks.add(sw);
        }
        return salesWeeks;
    }

    public Timestamp dateToTime(Date dt) throws ParseException {
        Calendar c = Calendar.getInstance();
        c.setTime(dt);
        long time = c.getTimeInMillis();
        long curr = System.currentTimeMillis();
        long diff = curr - time;    //Time difference in milliseconds
        return new Timestamp(time);
    }
}
